/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.model.DAO.rating;

import com.myapp.model.DAO.HibernateUtil;
import com.myapp.model.ejercicios.Ejercicios;
import com.myapp.model.resueltos.Resueltos;
import com.myapp.model.usuarios.Usuarios;
import java.text.DecimalFormat;
import java.util.List;
import org.hibernate.HibernateException;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author jaime
 */
public class RatingDao {

    /**
     * Metodo que calcula el Rating de un usuario
     * @param Usuarios u
     * @return String
     */
    public String getRating(Usuarios u) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        String rate = "0.0";
        String hql;
        int positivos;
        int total;
        try {
            session.beginTransaction();
            hql = "select count(*) from Resueltos r where usuarios=" + u.getIdusuario() + " and estados=7";
            total = Integer.parseInt(session.createQuery(hql).uniqueResult().toString());
            hql = "select sum(puntuacion) from Resueltos r where usuarios=" + u.getIdusuario() + " and estados=7";
            if (session.createQuery(hql).uniqueResult() != null || total != 0) {
                positivos = (Integer.parseInt(session.createQuery(hql).uniqueResult().toString())) * 100;
                DecimalFormat df = new DecimalFormat("###.#");
                rate = String.valueOf(df.format(positivos / total));
                session.getTransaction().commit();
            }
        } catch (Exception e) {
            session.getTransaction().rollback();
        } finally {
            if (session.isOpen() && session.getTransaction().isActive()) {
                session.getTransaction().commit();
            }
            return rate;
        }
    }

    public List threePlanets() {

        Session session = HibernateUtil.getSessionFactory().getCurrentSession();

        String hql;

        List lista = null;

        try {

            session.beginTransaction();

            hql = "select n.idnivel,n.titulo, count(u) "
                    + "from Nivel n left join n.usualengs u "
                    + "group by n.titulo "
                    + "order by count(u) desc "
                    + "limit 5";

            lista = session.createQuery(hql).list();

            session.getTransaction().commit();

        } catch (Exception e) {
            session.getTransaction().rollback();
        } finally {
            if (session.isOpen() && session.getTransaction().isActive()) {
                session.getTransaction().commit();
            }
            return lista;
        }
    }

    /**
     * Listado de usuarios con más puntuación
     * @return lista de usuarios
     */
    public List getUserMorePtos() {
        List usersMorePtos = null;
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        try {
            session.beginTransaction();
            usersMorePtos = session.createQuery("select u.alias, sum(r.puntuacion) "
                    + "from Resueltos r left join r.usuarios u "
                    + "group by u.alias "
                    + "order by sum(r.puntuacion) desc "
                    + "limit 5").list();

        } catch (Exception e) {
            session.getTransaction().rollback();
        }
        return usersMorePtos;
    }

    /**
     * Lista de usuarios que han propuesto más ejercicios
     * @return lista de usuarios
     */
    public List getUserMoreExerc() {
        List usersMoreExerc = null;
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        try {
            session.beginTransaction();
            usersMoreExerc = session.createQuery("select u.alias, count(e) "
                    + "from Ejercicios e left join e.usuarios u "
                    + "group by u.alias "
                    + "order by count(e) desc "
                    + "limit 5").list();
            session.getTransaction().commit();

        } catch (Exception e) {
            session.getTransaction().rollback();
        } finally {
            if (session.isOpen() && session.getTransaction().isActive()) {
                session.getTransaction().commit();
            }
            return usersMoreExerc;
        }
    }

    /**
     * Lista de ejercicios mças resueltso
     * @return lista de ejercicios
     */
    public List getExercMoreResolves() {
        List exerMoreResolve = null;
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        try {
            session.beginTransaction();
            exerMoreResolve = session.createQuery("select e.titulo, count(r) "
                    + "from Ejercicios e left join e.resueltoses r "
                    + "group by e.titulo "
                    + "order by count(r) desc "
                    + "limit 5").list();
            session.getTransaction().commit();

        } catch (Exception e) {
            session.getTransaction().rollback();
        } finally {
            if (session.isOpen() && session.getTransaction().isActive()) {
                session.getTransaction().commit();
            }
            return exerMoreResolve;
        }
    }
}
